#!/usr/bin/env python
# -*- coding: utf8 -*-

from __future__ import print_function
from __future__ import unicode_literals
from __future__ import absolute_import

import sys, json

if sys.version >= '3':
	unicode = str

class BaseQueryBuilder ( object ):
	def __init__ ( self, db ):
		self.db = db
		self._distinct = False
		self._fields = []
		self._tables = []
		self._joins = []
		self._conds = []
		self._groups = []
		self._havings = []
		self._orders = []
		self._limit = []

		# Parameters for parameterized queries
		self._params = []

	# {{{ SELECT
	def select ( self, * fields ):
		self._fields.extend ( fields )
		return self

	def select_max ( self, field, alias = None ):
		self._select ( field, 'MAX', alias )
		return self

	def select_min ( self, field, alias = None ):
		self._select ( field, 'MIN', alias )
		return self

	def select_avg ( self, field, alias = None ):
		self._select ( field, 'AVG', alias )
		return self

	def select_sum ( self, field, alias = None ):
		self._select ( field, 'SUM', alias )
		return self

	def _select ( self, field, func, alias ):
		if not alias:
			alias = field
		value = "%s(%s) AS %s" % ( func, field, alias )
		self._fields.append ( value )
	# }}}
	def distinct ( self ):
		self._distinct = True
		return self
	
	def table ( self, table, alias = None, fields = None, overwrite = False ):
		if overwrite: self._tables = []

		self._tables.append ( ( table, alias ) )

		if fields:
			if alias: 
				fields = [ "%s.%s" % ( alias, x ) for x in fields ]

			self.select ( * fields )
				
		return self

	# {{{ WHERE
	def where ( self, cond, * args ):
		return self._where ( "AND", cond, * args )

	def or_where ( self, cond, * args ):
		return self._where ( "OR", cond, * args )

	def _where ( self, mode, cond, * args ):
		if isinstance ( cond, list ):
			for c in cond:
				self._where ( mode, c [ 0 ], * c [ 1 ] )
			return self

		cond = self.db.placeholders ( cond )
		self._conds.append ( ( cond, mode ) )
		self._params.extend ( args )
		return self

	def where_in ( self, field, values ):
		self._where_in ( field, values, 'AND', False )
		return self

	def or_where_in ( self, field, values ):
		self._where_in ( field, values, 'OR', False )
		return self

	def where_not_in ( self, field, values ):
		self._where_in ( field, values, 'AND', True )
		return self

	def or_where_not_in ( self, field, values ):
		self._where_in ( field, values, 'OR', True )
		return self

	def _where_in ( self, field, values, mode, negate ):
		cond = [ field ]
		if negate:
			cond.append ( 'NOT' )
		cond.append ( 'IN' )

		if isinstance ( values, BaseQueryBuilder ):
			query, params = values.get_query_params ()
			cond.append ( '( %s )' % query )
			self._conds.append ( ( ' '.join ( cond ), mode ) )
			self._params.extend ( params )
			return

		if not isinstance ( values, list ):
			values = [ values ]

		cond.append ( '(' )
		cond.append ( ", ".join ( [ '?' ] * len ( values ) ) )
		cond.append ( ')' )

		cond = ' '.join ( cond )

		cond = self.db.placeholders ( cond )

		self._conds.append ( ( cond, mode ) )
		self._params.extend ( values )
	# }}}
	# {{{ LIKE
	def like ( self, field, value, like_mode = 'both' ):
		self._like ( field, value, like_mode, 'AND', False )
		return self

	def or_like ( self, field, value, like_mode = 'both' ):
		self._like ( field, value, like_mode, 'OR', False )
		return self

	def not_like ( self, field, value, like_mode = 'both' ):
		self._like ( field, value, like_mode, 'AND', True )
		return self

	def or_not_like ( self, field, value, like_mode = 'both' ):
		self._like ( field, value, like_mode, 'OR', False )
		return self

	def _like ( self, field, value, like_mode, mode, negate ):
		cond = [ field ]
		if negate:
			cond.append ( 'NOT' )
		cond.append ( 'LIKE %s' )
		cond = ' '.join ( cond )

		if like_mode in ( 'before', 'both' ):
			value = '%' + value

		if like_mode in ( 'after', 'both' ):
			value = value + '%'

		self._conds.append ( ( cond, mode ) )
		self._params.append ( value )

	# }}}

	def join ( self, table, cond, alias = None, mode = 'INNER' ):
		self._joins.append ( ( table, alias, cond, mode ) )
		return self

	def group ( self, group ):
		self._groups.append ( group )
		return self

	def having ( self, cond, * args ):
		self._havings.append ( ( cond, 'AND' ) )
		self._params.extend ( args )
		return self

	def or_having ( self, cond, * args ):
		self._havings.append ( ( cond, 'OR' ) )
		self._params.extend ( args )
		return self


	def order ( self, value ):
		self._orders.append ( value )
		return self

	def limit ( self, a, b = None ):
		self._limit = a, b
		return self

	def clone ( self ):
		import copy
		return copy.deepcopy ( self )


	# {{{ INTERNAL GETS
	def _get_fields ( self, sql ):
		if not self._fields:
			return
		sql.append ( "SELECT" )
		if self._distinct:
			sql.append ( 'DISTINCT' )
		sql.append ( ",".join ( self._fields ) )
 
	def _get_table ( self, sql ):
		if not self._tables:
			return
		sql.append ( 'FROM' )
		for table, alias in self._tables:
			sql.append ( table )
			if alias:
				sql.append ( "AS %s" % alias )
 
	def _get_joins ( self, sql ):
		if not self._joins:
			return
		for table, alias, cond, mode in self._joins:
			alias = 'AS %s' % alias if alias else ''
			sql.append ( '%s JOIN %s %s ON %s' % ( mode, table, alias, cond ) )
 
	def _get_where ( self, sql ):
		self._get_conds ( sql, 'WHERE', self._conds )
 
	def _get_group ( self, sql ):
		if not self._groups:
			return
		sql.append ( 'GROUP BY %s' % ",".join ( self._groups ) )

	def _get_having ( self, sql ):
		self._get_conds ( sql, 'HAVING', self._havings )
 
	def _get_order ( self, sql ):
		if not self._orders:
			return
		sql.append ( 'ORDER BY %s' % ",".join ( self._orders ) )
 
	def _get_limit ( self, sql ):
		if not self._limit:
			return
		sql.append ( 'LIMIT %s' % self._limit [ 0 ] )
		if self._limit [ 1 ] != None:
			sql.append ( ", %s" % self._limit [ 1 ] )

	def _get_conds ( self, sql, op, lst ):
		if not lst:
			return ''
		first = True
		sql.append ( op )
		for cond, mode in lst:
			if not first:
				sql.append ( mode )
			first = False
			sql.append ( cond )
	# }}}

	def as_count ( self ):
		import copy

		tmp = copy.copy ( self )
		tmp._fields = [ 'COUNT(*)' ]
		tmp._order = []
		tmp._limit = []

		return tmp

	def copy_from ( self, qb ):
		self._distinct = qb._distinct
		self._fields.extend ( db._fields )
		self._tables.extend ( db._tables )
		self._joins.extend ( db._joins )
		self._conds.extend ( db._conds )
		self._groups.extend ( db._groups )
		self._havings.extend ( db._havings )
		self._orders.extend ( db._orders )
		self._limit.extend ( db._limit )

		# Parameters for parameterized queries
		self._params.extend ( db._params )

		return self

	def get_query_params ( self ):
		return unicode ( self ), self._params

	def __repr__ ( self ):
		pass
		
	def __str__ ( self ):
		sql = []
		self._get_fields ( sql )
		self._get_table ( sql )
		self._get_joins ( sql )
		self._get_where ( sql )
		self._get_group ( sql )
		self._get_having ( sql )
		self._get_order ( sql )
		self._get_limit ( sql )
		return ' '.join ( sql )
		

class DBManagerException ( Exception ):
	pass

class DBMLiteral ( str ):
	pass

class DBManager ( object ):
	def __init__ ( self, db_type ):
		if db_type == 'mysql':
			from .drivers.mysql import Driver, QueryBuilder
		elif db_type == 'sqlite':
			from .drivers.sqlite import Driver, QueryBuilder
		else:
			raise DBManagerException ( "Driver not found for: %s" % db_type )

		self.db = Driver ()
		self.qbclass = QueryBuilder

	def connect ( self, ** kwargs ):
		self.db.connect ( ** kwargs )

	def close ( self ):
		self.db.close ()

	def qb ( self, query_data = None, allows = None ):
		qb = self.qbclass ( self.db )

		if not allows or not query_data: return qb

		return self._mk_query ( qb, query_data, allows )

	def raw ( self, s ):
		if isinstance ( s, BaseQueryBuilder ):
			s = unicode ( s )
		return DBMLiteral ( s )

	def debug ( self, msg ):
		import sys
		sys.stderr.write ( "DBManager: %s\n" % msg.strip () )

	def execute ( self, qb, params = None, debug = False ):
		debug = True
		try:
			#sys.stderr.write ( "\n\nEXECUTE: %s - %s\n\n" % ( qb, qb._params ) )
			self.db.execute ( qb, params )
			if debug: self.debug ( self.db._last_executed () )
			#self.debug ( self.db._last_executed () )

		except Exception as ex:
			# FIXME: sarebbe meglio controllare se la classe dell'eccezione e' una
			# sottoclasse di, per esempio, self.db.baseexception o qualcosa del genere
			err = ex.args [ 0 ] if len ( ex.args ) < 2 else ex.args [ 1 ]
			msg = "ERROR: %s - QUERY: %s" % ( err, self.db._last_executed () ) 
			self.debug ( msg )

	def all_rows ( self, qb, debug = False ):
		self.execute ( qb, debug = debug )
		return [ self._unjson ( row ) for row in self.db.fetchall () ]

	def iter_rows ( self, qb, debug = False ):
		self.execute ( qb, debug = debug )

		while True:
			row = self.db.fetchone ()
			if row:
				yield self._unjson ( row )
			else:
				raise StopIteration

	def single_row ( self, qb, debug = False ):
		self.execute ( qb, debug = debug )
		row = self.db.fetchone ()

		if not row:
			row = dict ()
		return self._unjson ( row )

	def _unjson ( self, row ):
		for k, v in row.items ():
			if unicode ( v ).startswith ( "__json__" ):
				row [ k ] = json.loads ( v [ 8 : ] )

		return row
			

	def count ( self, qb, debug = False ):
		self.execute ( qb.as_count (), debug = debug )
		res = self.db.fetchone ()
		if res :
			return res [ 'COUNT(*)' ]
		return 0

	def dataset ( self, qb, start = 0, rows = 0, debug = False ):
		if not rows: return {}

		qb.limit ( start, rows )

		tot_rows = self.count ( qb, debug = debug )

		return { "rows" : tot_rows, "start" : start }

	def insert ( self, table, dct, debug = False  ):
		keys, items, values = self._items_values ( dct )
		sql = "INSERT %s ( %s ) VALUES ( %s )" % ( table, ', '.join ( keys ), ', '.join ( items ) )
		sql = self.db.placeholders ( sql )
		return self.execute ( sql, values, debug = debug )

	def update ( self, table, dct, qb, debug = False ):
		keys, items, values = self._items_values ( dct )
		query, params = qb.get_query_params ()
		values.extend ( params )

		fields = [ "%s = %s" % ( k, v ) for k, v in zip ( keys, items ) ]

		sql = "UPDATE %s SET %s %s" % ( table, ', '.join ( fields ), query )
		sql = self.db.placeholders ( sql )
		return self.execute ( sql, values, debug = debug )

	def delete ( self, table, qb, debug = False ):
		query, params = qb.get_query_params ()
		
		sql = "DELETE FROM %s %s" % ( table, query )
		sql = self.db.placeholders ( sql )
		return self.execute ( sql, params, debug = debug )

	def truncate ( self, table, debug = False ):
		return self.execute ( "TRUNCATE TABLE %s" % table, None, debug = debug )
		

	def replace ( self, table, dct, qb, debug = False ):
		qb_count = qb.clone ()

		qb_count.table ( table, overwrite = True )
		tot = self.count ( qb_count )

		if tot:
			ret = self.update ( table, dct, qb, debug = debug )
		else:
			ret = self.insert ( table, dct, debug = debug )

		return ret

	def insert_id ( self ):
		return self.db.insert_id ()

	def _items_values ( self, dct ):
		keys = dct.keys ()
		values = []
		items = []

		for k, v in dct.items ():
			if isinstance ( v, ( dict, list ) ):
				v = "__json__" + json.dumps ( v )

			if isinstance ( v, DBMLiteral ):
				items.append ( v )
			else:
				items.append ( '?' )
				values.append ( v )

		return keys, items, values

	def _mk_query ( self, qb, qd, allows ):
		"""
		_mk_query ( qb, qd, allows ) 

		Prepares a query based on data in ``qd`` (query data)

		{
			"conds": [
				{ "field" : "name", "mode" : "like", "value" : "spider"  },
				{ "field" : "id_sect", "value" : 12, "op" : "OR" },
				{ "field" : "created", "mode" : ">=", "value" : "NOW" }
			],

			"order": [
				{ "field" : "price_vat", "mode" : "DESC" },
				{ "field" : "name" }
			],
			"group" : [ 'fied_1' ] 
		}
		"""

		modes = {
			"like" : "LIKE ( ? )",
			"=" : "= ?",
			"in" : "IN ( ? )",
			">" : "> ?",
			"<" : "< ?",
			">=": ">= ?",
			"<=": "<= ?",
			"!=": "<> ?"
		}


		conds = []
		order = []
		group = []

		import sys 
		
		if "conds" in allows:
			
			for cnd in qd.get ( "conds", [] ):
				op = cnd.get ( "op", "and" ).lower ()

				val = cnd.get ( "value", "" )
				mode = modes.get ( cnd.get ( "mode", "=" ) )

				if val == 'NOW':
					sql = "%s %s" % ( cnd [ 'field' ], mode )
					sql = sql.replace ( '?', 'NOW()' )
					if op == "and":
						qb.where ( sql )
					elif op == "or":
						qb.or_where ( sql )
					continue

				#FIXME Bundle : la modalità "IN" non funzionava controllare anche la modalità "LIKE"
				if cnd.get ( "mode", "" ).lower () == "in":
					if op == "and" :
						qb.where_in ( cnd [ 'field' ], val )
					elif op == "or":
						qb.or_where_in ( cnd [ 'field' ], val )
					
				else:
					sql = "%s %s" % ( cnd [ 'field' ], mode )
					if op == "and" :
						qb.where ( sql, val )
					elif op == "or":
						qb.or_where ( sql, val )
				

		if "order" in allows:
			for cnd in qd.get ( "order", [] ):
				qb.order ( "%s %s" % ( cnd [ 'field' ], cnd.get ( "mode", "ASC" ) ) )
	
		if "group" in allows:
			for cnd in qd.get ( "group", [] ):
				qb.group ( cnd )

		return qb
		

if __name__ == '__main__':
	dbm = DBManager ( 'mysql' )
	dbm.connect ( user = 'omen', db = 'omen', passwd = 'omen', charset = 'utf8', use_unicode = True )

	def dump ( dbm, q = None ):
		if not q:
			q = dbm.qb()
			q.select ( '*' ).table ( 'test' )
		print ( "*" * 80 )

		for row in dbm.all_rows ( q ):
			print ( "{id:<3} {txt:<50} {created:%Y-%m-%d %H:%M:%S} {mode:<10}".format ( ** row ) )

		print ( "*" * 80 )

	dbm.execute ( """
	CREATE TABLE test (
		id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
		txt TEXT,
		created DATETIME,
		mode varchar(50)
	)
	""")

	for x in range ( 10 ):
		dct = {
			'txt': 'Test text n.%s' % x,
			'created': dbm.raw ( 'NOW() - INTERVAL %s DAY' % x ),
			'mode': 'PROG'
		}
		dbm.insert ( 'test', dct )

	x = 4

	q = dbm.qb ()
	q.select ( 'id', 'txt', 'created', 'mode' )
	q.table ( 'test' )
	q.where ( 'id = ?', 2 )
	q.or_where ( 'id = ?', x )
	dump ( dbm, q )

	q = dbm.qb ()
	q.select ( 'id', 'error' )
	q.table ( 'test' )
	dump ( dbm, q )

	q = dbm.qb ()
	q.where ( 'id = ?', 4 )
	dbm.update ( 'test', { 'created' : dbm.raw ( 'NOW() - INTERVAL 30 DAY' ) }, q )
	dump ( dbm )

	q = dbm.qb ()
	q.where ( 'id = ?', 4 )
	dbm.delete ( 'test', q )
	dump ( dbm )

	print ( "*** Prova iter_rows ***" )
	q = dbm.qb()
	q.select ( '*' ).table ( 'test' )
	#q.where ( 'id = ?', 43456742 )
	for row in dbm.iter_rows ( q ):
		print ( "{id:<3} {txt:<50} {created:%Y-%m-%d %H:%M:%S} {mode:<10}".format ( ** row ) )

	print ( "*" * 80 )

	dbm.execute ( "DROP TABLE test" )

